<?php
/**********************************************************************
 *                                                                    *    
 * Copyright (c) 2009 Dmytro Bezhnar. All rights reserved.     *
 *                                                                    *
 **********************************************************************/

/**
 * Ageset entity manager.
 *
 * @version $Id: NewsCommonManager.php,v 1.152 2008/10/13 14:52:25 megadevel Exp $
 */
class NewsCommonManager extends ORMManager 
{
	#var $min_news_id=93122;
	var $min_news_id=112624;
	
	
	function addCommonReiting($news_id, $bal, $db, $project_id)
	{
		$bal*=1;
		$qs = "UPDATE 
					$db.news_reiting 
				SET 
					reiting_click=reiting_click+1, 
					common_reiting=common_reiting+$bal 
				WHERE news_id=$news_id";
		
		$this->execQuery($qs);
	}
	
	function getNewsListByIdList($id_array, $project_id, $db)
	{
		global $options;
		$passport_database = $options['passport']['database'];
		
		$where = ' and (0';
		if (count($id_array))
		foreach ($id_array as $item)
		{
			$where .= ' or news_id='.$item['news_id'];
		}
		$where .= ')';
		$qs = "SELECT 
					n.news_id, n.title, n.vrezka, pp.base_url
				FROM 
					$db.news n, 
					$passport_database.projects pp 
				WHERE 
					n.main_project_id=pp.project_id 
					$where";
		return $this->getArray($qs, 60*15);
	}
	
	function getStatisticByNewsList($project_id, $db, $id_array)
	{
		$where = ' and (0';
		if (count($id_array))
		foreach ($id_array as $key=>$value)
		{
			$tmp = $key*1;
			if ($tmp)
			{
				$where .= ' or news_id='.$tmp;
			}
		}
		$where .= ')';
		$qs = "SELECT 
					* 
				FROM 
					$db.news_reiting 
				where 1 $where ";
				
		return $this->getArray($qs);
	}
	
	function getAuthorByCityId($city_id, $db, $project_id)
	{
		$qs = "SELECT 
					author_id, fio 
				FROM 
					$db.authors
				WHERE 
					city_id=$city_id  
				order by 
					fio";
		return $this->getAssArray($qs, 'author_id', 'fio');
	}
	
	
	function getRubricById($project_id, $db, $rubric_id=0)
	{
		$qs = "SELECT 
					* 
				FROM 
					$db.rubrics 
				WHERE 
					lang_id=".$_SESSION['lang_id']." and 
					project_id=$project_id and 
					rubric_id=$rubric_id ";
		return $this->getOneArray($qs);
	}
	
	function getRubricsList($project_id, $db)
	{
		$qs = "SELECT 
					* 
				FROM 
					$db.rubrics 
				WHERE 
					lang_id=".$_SESSION['lang_id']." and 
					project_id=$project_id and 
					is_visible=1 
				ORDER BY 
					order_id ";
		#print '<!-- ';
		#print $qs;
		#print_r($ret);
		#print ' -->';
		return $this->getArray($qs,60);
	}
	
	function getMostCommentNews($project_id, $db, $args, $count_days=7)
	{
		
		$where = '';
		
		if ($args['city_id']*1){
			$where .= ' and n.city_id='.$args['city_id']*1;
		}else{
			$where .= ' and n.city_id<>30';
		}
		
		if ($args['rubric_id']*1)
		{
			$where .= ' and pn.rubric_id='.$args['rubric_id']*1;
		}
		
		$qs = "SELECT 
						news_id  
					FROM 
						$db.news 
					where 
						add_date>=date_sub(now(),INTERVAL $count_days DAY) 
					order by 
						news_id  
					limit 1";
		$min_id = $this->getOneArray($qs,2*60*60);
		
		$qs = "SELECT 
					n.news_id, n.title, nr.comments_count 
				FROM 
					$db.news_reiting nr,  
					$db.project_news pn, 
					$db.news n 
				WHERE 					
					pn.news_id > ".$min_id['news_id']." and 
					nr.news_id=pn.news_id and 
					n.news_id=nr.news_id and 
					pn.rubric_id<>46 and 
					pn.rubric_id<>70 and 
					pn.project_id=$project_id 
					$where 
				ORDER BY 
					nr.comments_count desc, n.news_id desc
				LIMIT 10";
		return $this->getArray($qs,5*60);
	}
	
	function getMostViewNews($project_id, $db, $args, $count_days=7)
	{
		
		$where = '';
		
		if ($args['city_id']*1){
			$where .= ' and n.city_id='.$args['city_id']*1;
		}else {
			$where .= ' and n.city_id<>30 ';
		}
		
		
		if ($args['rubric_id']*1)
		{
			$where .= ' and pn.rubric_id='.$args['rubric_id']*1;
		}
		
		$qs = "SELECT 
						news_id  
					FROM 
						$db.news 
					where 
						add_date>=date_sub(now(),INTERVAL $count_days DAY) 
					order by 
						news_id  
					limit 1";
		$min_id = $this->getOneArray($qs,5*60);
		
		
		
		$qs = "SELECT 
					n.news_id, n.title, nr.view_count 
				FROM 
					$db.news_reiting nr, 
					$db.project_news pn,  
					$db.news n 
				WHERE 					
					pn.news_id > ".$min_id['news_id']." and 
					nr.news_id=pn.news_id and 
					n.news_id=nr.news_id and 
					pn.rubric_id<>46 and 
					pn.rubric_id<>70 and 
					pn.project_id=$project_id 
					$where 
				ORDER BY 
					nr.view_count desc, n.news_id desc
				LIMIT 10";
		
		
		
		return $this->getArray($qs, 5*60);
	}
	
	function getTopNews($news_type_id, $limit, $args, $project_id, $db)
	{
		
		$where = '';
		
		if ($args['city_id']*1){
			$where .= ' and city_id='.$args['city_id']*1;
		}else {
			$where .= ' and city_id<>30 ';
		}
		
		
		
		$where .= ' and rubric_id='.$args['rubric_id']*1;
		
		
		$qs = "SELECT 
					news_id
				FROM 
					$db.top_news 
				WHERE 
					project_id=$project_id 
					$where 
					and status_id=$news_type_id 
				order by 
					top_id desc
				LIMIT $limit";

		#print '<!-- ';		
		#print $qs;
		#print ' -->';
		return $this->getArray($qs,60*5);
	}
	
	function updateNewsReitingCommentsCount($news_id, $increment, $db)
	{		
		$qs = "update 
					$db.news_reiting 
				SET 
					comments_count=comments_count+$increment 
				where 
					news_id=".$news_id*1;
		$this->execQuery($qs);
	}
	function updateNewsReiting($news_id, $increment, $db)
	{
		$qs = "update 
					$db.news_reiting 
				SET 
					reiting_click=reiting_click+1, 
					common_reiting=common_reiting+$increment 
				where 
					news_id=".$news_id*1;
		$this->execQuery($qs);
	}
	
	function updateViewCountForNews($news_id, $db)
	{		
		$qs = "update 
						$db.news_reiting 
					SET 
						view_count=view_count+1, 
						last_view_date=now() 
					where 
						news_id=".$news_id*1;
		$this->execQuery($qs);
	}
	
	function getNewsById($news_id, $project_id, $db)
	{			
		global $options;
		$passport_database = $options['passport']['database'];
		
		$news_id*=1;
		
		$qs = "SELECT 
					n.news_id, n.title, n.vrezka, n.photo, n.descript_photo, n.add_date, n.with_video, n.video, TO_DAYS(NOW()) - TO_DAYS(n.add_date) count_days, 
					n.video_photo, n.source_name, n.source_url, n.city_id, n.author_id, 
					nt.text, 
					r.rub_name, r.rubric_id, r.link as rubric_link, 
					a.fio, a.photo author_photo, a.telephone, a.email,
					c.name city_name, 
					nr.view_count, nr.comments_count, nr.reiting_click, 
					if(nr.reiting_click=0,0,ROUND(nr.common_reiting/nr.reiting_click)) reiting_avg, 
					pp.base_url 
				FROM 
					$db.project_news p, 
					$db.news n 
					left join $db.authors a on n.author_id=a.author_id 
					left join $passport_database.ocities c on n.city_id=c.cityID and c.lang_id=".$_SESSION['lang_id'].", 
					$db.news_text nt, 
					$db.rubrics r, 
					$db.news_reiting nr, 					 
					$passport_database.projects pp		
				where 
					p.project_id=$project_id and 
					p.news_id=n.news_id and 					
					p.rubric_id=r.rubric_id and r.lang_id=".$_SESSION['lang_id']." and 
					n.news_id=nr.news_id and 
					n.news_id=nt.news_id and 					
					n.main_project_id=pp.project_id and 
					p.news_id=$news_id";
		return $this->getOneArray($qs, 60*3);		
	}
	
	
	function getYesterdayMostCommentedNewsId($project_id,$db,$args)
	{
		if ($args['limit']*1){
			$count_page = $args['limit']*1;
		}else {
			$count_page = 10;
		}
		$min = $args['page']*$count_page;
		
		$where = '';
		if ($args['city_id']*1){
			$where .= ' and n.city_id='.$args['city_id']*1;
		}else {
			$where .= ' and n.city_id<>30 ';
		}
		
		
		
		if ($args['rubric_id']*1)
		{
			$where .= ' and pn.rubric_id='.$args['rubric_id']*1;
		}
		
		if ($args['author_id']*1)
		{
			$where .= ' and n.author_id='.$args['author_id']*1;
		}
		
		
		
		$qs = "SELECT 
					sql_calc_found_rows
					n.news_id, 
					count(nc.obj_id) as cnt		
				FROM 
					news_common.comments nc, 
					news_common.project_news pn, 
					news_common.news n
				WHERE 
					nc.obj_type_id=1 and 
					nc.create_date >= curdate() and 
					nc.obj_id=pn.news_id and 
					nc.obj_id=n.news_id and 
					pn.project_id=$project_id 
					$where 
				GROUP BY 
					nc.obj_id 
				order by 
					cnt desc, 
					nc.obj_id desc 
				LIMIT $min,$count_page";		
		$data = $this->getArray($qs);
				
		$qs = "SELECT FOUND_ROWS() cnt";
		$cnt = $this->getOneArray($qs);
		
		return array('count' => $cnt['cnt'], 'data' => $data);
	}
	
	
	function getNewsListId(&$dialog, $args)
	{
		if ($args['limit']*1){
			$count_page = $args['limit']*1;
		}else {
			$count_page = 10;
		}
		
		$min = $args['page']*$count_page;
		
		
		
		$where = '';		
				
		if ($args['day_from'] && $args['mons_from'] && $args['year_from'])
		{
			$date_from 		= date('Y-m-d H:i:s', mktime(0, 0, 0, $args['mons_from'], $args['day_from'], $args['year_from']));			
			$date_to  = date('Y-m-d H:i:s', mktime(23, 59, 59, $args['mons_from'], $args['day_from'], $args['year_from']));
			
			$where.= " and (p.add_date BETWEEN '$date_from' and '$date_to') "; 			
		}		
		
		
		if ($args['city_id']*1){
			$where .= ' and n.city_id='.$args['city_id']*1;
		}else {
			$where .= ' and n.city_id<>30 ';
		}
		
		if ($args['rubric_id']*1)
		{
			$where .= ' and p.rubric_id='.$args['rubric_id']*1;
		}		
		
		if (count($args['rubrics_list']))
		{
			$where .= ' and (0';
			foreach ($args['rubrics_list'] as $item)
			{
				$where .= ' or rubric_id='.$item;
			}
			$where .= ') ';
		}	
		
		
		if ($args['author_id']*1)
		{
			$where .= ' and n.author_id='.$args['author_id']*1;
		}
		
				
		if ($args['with_video']*1)
		{			
			$where .= ' and n.with_video ';			
		}
		
		if ($args['where'])
		{			
			$where .= $args['where'];
		}
		
		$qs = "SELECT 
					sql_calc_found_rows 
					n.news_id 
				FROM 
					$dialog->project_db.project_news p, 
					$dialog->project_db.news n
				where 
					p.news_id > $this->min_news_id and 
					p.project_id=$dialog->project_id and 
					p.rubric_id<>46 and 
					p.rubric_id<>70 and 
					p.news_id=n.news_id 
					$where 
				order by 
					n.news_id desc	
				limit $min, $count_page";
		
		#print '<!-- ';		
		#print $qs;
		#print ' -->';
		
		$data = $this->getArray($qs);
		
		$qs = "SELECT FOUND_ROWS() cnt";
		$cnt = $this->getOneArray($qs);
		
		return array('count' => $cnt['cnt'], 'data' => $data);
		
	}
	
	function getNewsListExtended($id, $project_id, $db)
	{			
		global $options;
		$passport_database = $options['passport']['database'];
		
		$where = ' and (0';
				
		foreach ($id as $item)
		{
			$where .= ' or n.news_id='.$item['news_id'];
		}
		$where .= ') ';
		
		$qs = "SELECT 					
					n.news_id, n.title, n.vrezka, n.photo, n.descript_photo, n.add_date, n.with_video, n.video, TO_DAYS(NOW()) - TO_DAYS(n.add_date) count_days, 			
					n.video_photo, n.source_name, n.source_url, n.main_project_id, DATE_FORMAT(n.add_date,'%a, %e %b %Y %H:%i:%s +0200') as date_rss, TO_DAYS(NOW()) - TO_DAYS(n.add_date) count_days, 
					date_format(n.add_date,'%H:%i') time, date_format(n.add_date,'%d/%m/%Y') date, 
					r.rub_name, r.rubric_id, 
					a.fio, a.photo author_photo, a.telephone, 
					c.name city_name, 
					nr.view_count, nr.comments_count, nr.reiting_click, 
					if(nr.reiting_click=0,0,ROUND(nr.common_reiting/nr.reiting_click)) reiting_avg, 
					pp.base_url 
				FROM 
					$db.project_news p, 
					$db.news n 
					left join $db.authors a on n.author_id=a.author_id
					left join $passport_database.ocities c on n.city_id=c.cityID and c.lang_id=".$_SESSION['lang_id'].",  		
					$db.rubrics r, 
					$db.news_reiting nr, 					
					$passport_database.projects pp		
				where 					
					p.news_id=n.news_id and 
					p.project_id=$project_id and 
					p.rubric_id=r.rubric_id and r.lang_id=".$_SESSION['lang_id']." and 
					n.news_id=nr.news_id and 					
					n.main_project_id=pp.project_id 
					$where 
				order by 
					n.news_id desc ";
		

		#print '<!-- ';		
		#print $qs;
		#print ' -->';
		return $this->getArray($qs, 60*15);
	}
	
	
	
	function getNewsList(&$dialog, $arg)
	{
		global $options;
		$passport_database = $options['passport']['database'];
		
		$where = '';
		
		if ($arg['city_id']*1){
			$where .= ' and n.city_id='.$arg['city_id']*1;
		}else {
			$where .= ' and n.city_id<>30 ';
		}
		
		if ($arg['with_video']*1)
		{
			$where .= ' and n.with_video=1';
		}
		
		if ($arg['rubric_id']*1)
		{
			$where .= ' and p.rubric_id='.$arg['rubric_id']*1;
		}
		
		
		if ($arg['author_id']*1)
		{
			$where .= ' and n.author_id='.$arg['author_id']*1;
		}
		
		
		$db = $dialog->project_db;
		
		$where_tags = '';
		
		if ($arg['news_id_for_tag_list'])
		{
			$where_tags = "and p.news_id in (
							select 
									nt.news_id 
								FROM 
									$db.news_tags nt 
								where 
									nt.news_id>$this->min_news_id and 
									nt.tag_id in (SELECT 
												nt1.tag_id 
											FROM 
												$db.news_tags nt1 
											WHERE 
												news_id=".$arg['news_id_for_tag_list']."))";
		}		
		
		
		$limit = $arg['limit']*1;
		if (!$limit){$limit=10;}
		
		$project_id=$dialog->project_id*1;
		if (!$project_id){$project_id=5;}
		
		
		
		
		$qs = "";
		
		
		$qs = "SELECT 
					n.news_id, n.title, n.add_date, n.with_video, n.photo, n.main_project_id, pp.project_id, pp.base_url, pp.icon, TO_DAYS(NOW()) - TO_DAYS(n.add_date) count_days, 
					date_format(n.add_date,'%H:%i') time, date_format(n.add_date,'%d/%m/%Y') date
				FROM 
					$db.project_news p, 
					$db.news n, 
					$passport_database.projects pp 
				where 
					p.news_id > $this->min_news_id and 
					p.project_id=$project_id and 
					p.news_id=n.news_id and 
					p.rubric_id<>46 and 
					p.rubric_id<>70 and 
					n.main_project_id=pp.project_id 
					$where 
					$where_tags 
				order by  
					n.news_id desc 
				limit $limit";
		
		#print '<!-- ';
		#print $qs;
		#print ' -->';
		
		
		return $this->getArray($qs,60*15);		
	}
}

?>